package com.order.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

public class MenuDBUtil 
{
	 static String driver="org.gjt.mm.mysql.Driver";
	 static String url="jdbc:mysql://localhost:3306/test";
	 static	String user="root";	
	 public static List<String[]> lookmenu()
	 {
		 List<String[]> ll=new ArrayList<String[]>();
		 try
		 {
			    List<String> li=MenuDBUtil.getcatename();
			    Class.forName(driver);
				Connection con=DriverManager.getConnection(url,user,"");
				Statement st=con.createStatement();
				String sql=null;
				ResultSet rs=null;
				for(String s:li)	
				{
					sql="select cate_name,vege_name,vege_price,vege_good,vt_name from Vege,Category,Vegetype where Vege.cate_id= '"+s+"' and " +
							"Vege.cate_id=Category.cate_id and Vege.vt_id=Vegetype.vt_id";
				rs=st.executeQuery(sql);
				while(rs.next())
				{
					String[] ss=new String[5];
					for(int i=0;i<ss.length;i++)
					{
						ss[i]=rs.getString(i+1);
					}
					ll.add(ss);
				}
				return ll;
				}
				rs.close();
				st.close();
				con.close();
				
		 }
		 catch(Exception e)
		 {
			 e.printStackTrace();
		 }
		 return ll;
	 }
	 public static void main(String args[])
	 {
		 List<String[]> ll=MenuDBUtil.lookmenu();
		 for(String[] s:ll)
		 {
			 for(int i=0;i<s.length;i++)
			 {
				 System.out.print(s[i]);
			 }
		 }
	 }
	 public static List<String> getcatename()
	 {
		  List<String> ll=new ArrayList<String>();
		 try
		 {
			    Class.forName(driver);
				Connection con=DriverManager.getConnection(url,user,"");
				Statement st=con.createStatement();
				String sql="select cate_id from Category";
				ResultSet rs=st.executeQuery(sql);
				while(rs.next())
				{
					String str=null;
					str=rs.getString(1);
					ll.add(str);
				}
		 }catch(Exception e)
		 {
			 e.printStackTrace();
		 }
		 return ll;
	 }
}
